﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace GeneralMethodClass.Office
{
    public class ExcelOP
    {
        /// <summary>
        /// 加载Excel 返回值是一个DataSet
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheet">工作表名</param>
        /// <returns>DataSet</returns>
        public static DataSet LoadDataFromExcel(string filePath, string sheet)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM [" + sheet + "$]";

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, sheet);
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                MessageBox.Show("加载Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }

        /// <summary>
        /// 将DataTable数据写入Excel
        /// </summary>
        /// <param name="excelTable">要写入的DataTable</param>
        /// <param name="filePath">保存路径</param>
        /// <returns>bool</returns>
        public static bool SaveDataTableToExcel(DataTable excelTable, string filePath)
        {
            Excel.Application app = new Excel.Application();
            try
            {
                app.Visible = false;
                Excel.Workbook wBook = app.Workbooks.Add(true);
                Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;

                if (excelTable.Rows.Count > 0)
                {
                    int row = 0;
                    row = excelTable.Rows.Count;
                    int col = excelTable.Columns.Count;

                    for (int i = 0; i < row; i++)
                    {
                        for (int j = 0; j < col; j++)
                        {
                            string str = excelTable.Rows[i][j].ToString();
                            wSheet.Cells[i + 2, j + 1] = str;
                        }
                    }
                }

                int size = excelTable.Columns.Count;

                for (int j = 0; j < size; j++)
                {
                    Excel.Range range = wSheet.Columns[j + 1];

                    if (excelTable.Columns[j].DataType.ToString() == "System.String")
                        range.NumberFormat = "@";
                    else if (excelTable.Columns[j].DataType.ToString() == "System.Double")
                        range.NumberFormat = "#0.00";
                    else if (excelTable.Columns[j].DataType.ToString() == "System.DateTime")
                        range.NumberFormat = "yyyy-mm-dd";
                }

                for (int i = 0; i < size; i++)
                {
                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
                }
                //设置禁止弹出保存和覆盖的询问提示框 
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿 
                wBook.Save();
                //保存excel文件 
                app.Save(filePath);
                app.SaveWorkspace(filePath);
                app.Quit();
                app = null;
                return true;
            }
            catch (Exception err)
            {
                MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {

            }
        }

        /// <summary>
        /// 获取Excel中所有工作表名称
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns>List</returns>
        public static List<string> GetSheetNames(string filePath)
        {
            Excel.Application excelApp = new Excel.Application();
            object objOpt = Missing.Value;
            Excel._Workbook wbclass = (Excel._Workbook)excelApp.Workbooks.Open(filePath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);

            List<string> list = new List<string>();
            Excel.Sheets sheets = wbclass.Worksheets;
            string sheetNams = string.Empty;

            foreach (Excel.Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }

            excelApp.Quit();
            excelApp = null;
            return list;
        }

        /// <summary>
        /// 打开Excel文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        public static void OpenExcel(string filePath)
        {
            try
            {
                Excel.Application excelApp = new Excel.Application();
                Excel.Workbook wkb = excelApp.Workbooks.Add(filePath);
                excelApp.Visible = true;
            }
            catch (Exception err)
            {
                MessageBox.Show("打开Excel时出错！错误原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}